package com.bj58.data.emailreports.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bj58.data.emailreports.dao.SrcDbPool.DBType;
import com.bj58.data.emailreports.model.SeoWeekVo;
import com.bj58.data.emailreports.service.SeoWeekReportService;
import com.bj58.data.emailreports.utils.CommonDataDeal;
import com.bj58.data.emailreports.utils.DateUtil;

public class SeoWeekReportServiceImpl implements SeoWeekReportService {
	private CommonDataDeal comDeal = new CommonDataDeal();
	
	@Override
	public List<SeoWeekVo> searchData(List<String> dateList , String tableName, int type)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		
		List<String> cols = new ArrayList<String>();
		cols.add("statDate");cols.add("product");
		cols.add("uv");cols.add("seoUv");cols.add("seoRate");
		
		//seo souce   t_netflow_pc_pv_   = SEO    t_netflow_m_pv_ = sou
		String seoSource = "";
		if(tableName.contains("t_netflow_pc_pv_")){
			seoSource = "SEO";
		}else if(tableName.contains("t_netflow_m_pv_")){
			seoSource = "sou";
		}
		
		
		for(String searchDate : dateList){
			
			//判断是否跨月
			String searchBeginDate =DateUtil.getStringDate(DateUtil.getDateBefore(DateUtil.strToDate(searchDate, "yyyy-MM-dd"), 6), "yyyy-MM-dd") ;
			String searchEndDate = searchDate;
			List<String> monthList =  DateUtil.subSearchDate(searchBeginDate, searchEndDate);
			boolean isTwoMonth = false;
			if(monthList.size() == 2){
				isTwoMonth = true;
			}
			StringBuffer sql = new StringBuffer();
			
			if(!isTwoMonth){ //非跨月
				String month = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
				
				sql.append(" select '"+searchDate+"', business_type,  ");
				sql.append(" sum( CASE source1 when 'all' then uv else 0 end )/7 as '总UV', ");
				sql.append(" sum( CASE source1 when '"+seoSource+"' then uv else 0 end )/7  as 'SEO-UV',  ");
				sql.append(" (sum( CASE source1 when '"+seoSource+"' then uv else 0 end )/7)/(sum( CASE source1 when 'all' then uv else 0 end )/7) as 'SEO占比' ");
				sql.append(" FROM  " + tableName + month );
				sql.append(" WHERE stat_date >='"+searchBeginDate+"' and stat_date<='"+searchEndDate+"' ");
				if(type == 1){ //总体
					sql.append(" and disp_city1_name = 'all'  ");
				}else if(type == 2){ //中小城市
					sql.append(" and disp_city1_name in  ('菏泽','临汾','枣庄','晋城','张家口','大同','濮阳','周口','滨州','绥化','丹东','揭阳','盘锦','承德','葫芦岛','宜宾','运城','长治','晋中','四平','遂宁','清远','鸡西','玉溪','宿迁','白城','朝阳','驻马店','达州','辽阳','本溪','茂名','贵港','安康','萍乡','通辽','铁岭','庆阳','乌海','衢州','阜新','汉中','鹤壁','怀化','济源','郴州','娄底','莱芜','攀枝花','三门峡','大理','丽江','邵阳','孝感','阳泉','通化','德宏','梧州','钦州','忻州','朔州','吴忠','呼伦贝尔','吕梁','河源','广元','巴彦淖尔市','辽源','丽水','玉林','乌兰察布','景德镇','阳江','永州','眉山','鹤岗','凉山','恩施','鄂州','随州','红河','韶关','益阳','资阳','广安','抚州','毕节','伊春','咸宁','黑河','梅州','贺州','双鸭山','六盘水','潮州','白银','兴安盟','巴中','武威','黔东南','安顺','延安','锡林郭勒','黔南','楚雄','中卫','铜川','张家界','阿克苏','平凉','河池','张掖','商洛','雅安','文山','临沧','昭通','汕尾','哈密','铜仁','白山','定西','来宾','防城港','喀什','大兴安岭','仙桃','酒泉','七台河','固原','海拉尔','金昌','潜江','百色','普洱','云浮','保山','湘西','阿拉善盟','那曲','甘孜','陇南','张北','黔西南','临夏','天门','西双版纳','嘉峪关','博尔塔拉','崇左','甘南','阿坝','海西','迪庆','怒江','神农架','昌都','果洛','黄南','海南','海东','香港','林芝','海北','五家渠','山南','三沙','和田','玉树','吐鲁番','台湾','澳门','阿里','阿拉尔','五指山','日喀则','克孜勒苏','图木舒克','马来西亚','新加坡','泰国') ");
				}
				sql.append(" and page_type ='all' ");
				sql.append(" and disp_cate1_name = 'all'  ");
				sql.append(" group by business_type ");

			}else{//跨月   union
				sql.append(" select '"+searchDate+"', business_type,  ");
				sql.append(" sum(all_uv)/7, sum(seo_uv)/7, (sum(seo_uv)/7)/(sum(all_uv)/7) from  ");
				sql.append("	( ");
				sql.append(" select business_type, sum( CASE source1 when 'all' then uv else 0 end ) as all_uv, sum( CASE source1 when '"+seoSource+"' then uv else 0 end ) as seo_uv  ");
				sql.append(" FROM  " + tableName + monthList.get(0).split(";")[0]);
				sql.append(" WHERE stat_date >='"+searchBeginDate+"' and stat_date<='"+searchEndDate+"' ");
				if(type == 1){ //总体
					sql.append(" and disp_city1_name = 'all'  ");
				}else if(type == 2){ //中小城市
					sql.append(" and disp_city1_name in  ('菏泽','临汾','枣庄','晋城','张家口','大同','濮阳','周口','滨州','绥化','丹东','揭阳','盘锦','承德','葫芦岛','宜宾','运城','长治','晋中','四平','遂宁','清远','鸡西','玉溪','宿迁','白城','朝阳','驻马店','达州','辽阳','本溪','茂名','贵港','安康','萍乡','通辽','铁岭','庆阳','乌海','衢州','阜新','汉中','鹤壁','怀化','济源','郴州','娄底','莱芜','攀枝花','三门峡','大理','丽江','邵阳','孝感','阳泉','通化','德宏','梧州','钦州','忻州','朔州','吴忠','呼伦贝尔','吕梁','河源','广元','巴彦淖尔市','辽源','丽水','玉林','乌兰察布','景德镇','阳江','永州','眉山','鹤岗','凉山','恩施','鄂州','随州','红河','韶关','益阳','资阳','广安','抚州','毕节','伊春','咸宁','黑河','梅州','贺州','双鸭山','六盘水','潮州','白银','兴安盟','巴中','武威','黔东南','安顺','延安','锡林郭勒','黔南','楚雄','中卫','铜川','张家界','阿克苏','平凉','河池','张掖','商洛','雅安','文山','临沧','昭通','汕尾','哈密','铜仁','白山','定西','来宾','防城港','喀什','大兴安岭','仙桃','酒泉','七台河','固原','海拉尔','金昌','潜江','百色','普洱','云浮','保山','湘西','阿拉善盟','那曲','甘孜','陇南','张北','黔西南','临夏','天门','西双版纳','嘉峪关','博尔塔拉','崇左','甘南','阿坝','海西','迪庆','怒江','神农架','昌都','果洛','黄南','海南','海东','香港','林芝','海北','五家渠','山南','三沙','和田','玉树','吐鲁番','台湾','澳门','阿里','阿拉尔','五指山','日喀则','克孜勒苏','图木舒克','马来西亚','新加坡','泰国') ");
				}
				sql.append(" and disp_cate1_name = 'all'  ");
				sql.append(" and page_type ='all'  ");
				sql.append(" group by 1 ");
				sql.append(" union ");

				sql.append(" select business_type, sum( CASE source1 when 'all' then uv else 0 end ) as all_uv, sum( CASE source1 when '"+seoSource+"' then uv else 0 end ) as seo_uv  ");
				sql.append(" FROM  " + tableName + monthList.get(1).split(";")[0]);
				sql.append(" WHERE stat_date >='"+searchBeginDate+"' and stat_date<='"+searchEndDate+"' ");
				if(type == 1){ //总体
					sql.append(" and disp_city1_name = 'all'  ");
				}else if(type == 2){ //中小城市
					sql.append(" and disp_city1_name in  ('菏泽','临汾','枣庄','晋城','张家口','大同','濮阳','周口','滨州','绥化','丹东','揭阳','盘锦','承德','葫芦岛','宜宾','运城','长治','晋中','四平','遂宁','清远','鸡西','玉溪','宿迁','白城','朝阳','驻马店','达州','辽阳','本溪','茂名','贵港','安康','萍乡','通辽','铁岭','庆阳','乌海','衢州','阜新','汉中','鹤壁','怀化','济源','郴州','娄底','莱芜','攀枝花','三门峡','大理','丽江','邵阳','孝感','阳泉','通化','德宏','梧州','钦州','忻州','朔州','吴忠','呼伦贝尔','吕梁','河源','广元','巴彦淖尔市','辽源','丽水','玉林','乌兰察布','景德镇','阳江','永州','眉山','鹤岗','凉山','恩施','鄂州','随州','红河','韶关','益阳','资阳','广安','抚州','毕节','伊春','咸宁','黑河','梅州','贺州','双鸭山','六盘水','潮州','白银','兴安盟','巴中','武威','黔东南','安顺','延安','锡林郭勒','黔南','楚雄','中卫','铜川','张家界','阿克苏','平凉','河池','张掖','商洛','雅安','文山','临沧','昭通','汕尾','哈密','铜仁','白山','定西','来宾','防城港','喀什','大兴安岭','仙桃','酒泉','七台河','固原','海拉尔','金昌','潜江','百色','普洱','云浮','保山','湘西','阿拉善盟','那曲','甘孜','陇南','张北','黔西南','临夏','天门','西双版纳','嘉峪关','博尔塔拉','崇左','甘南','阿坝','海西','迪庆','怒江','神农架','昌都','果洛','黄南','海南','海东','香港','林芝','海北','五家渠','山南','三沙','和田','玉树','吐鲁番','台湾','澳门','阿里','阿拉尔','五指山','日喀则','克孜勒苏','图木舒克','马来西亚','新加坡','泰国') ");
				}
				sql.append(" and disp_cate1_name = 'all'  ");
				sql.append(" and page_type ='all'  ");
				sql.append(" group by 1 ");
				sql.append(" ) a ");
				sql.append(" group by business_type ");
			}
			
			sql.append(" :"+ DBType.db_94_data_app.getValue());
			
			sqlMap.put(sql.toString(),cols);
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("product");
		
		List<SeoWeekVo> rsList =  comDeal.AssembledDataBySqlMap(sqlMap, SeoWeekVo.class, gbList);
		for (SeoWeekVo vo : rsList) {
			String product = vo.getProduct();
			if("all".equals(product)){
				vo.setProduct("全部");
			}else if("ershou".equals(product)){
				vo.setProduct("二手");
			}else if("ershouche".equals(product)){
				vo.setProduct("二手车");
			}else if("fangchan".equals(product)){
				vo.setProduct("房产");
			}else if("huangye".equals(product)){
				vo.setProduct("黄页");
			}else if("other".equals(product)){
				vo.setProduct("其他");
			}else if("zhaopin".equals(product)){
				vo.setProduct("招聘");
			}else if("huangye-other".equals(product)){
				vo.setProduct("黄页-其他");
			}
		}
		
		return rsList;
	}

}
